using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DotNetSide.SqlScriptGenerator {
	class DatabaseHelper {
		public static DataTable GetTableSchema(string databaseName, string tableName, SqlConnection conn) {
			string[] dbext = tableName.Split(char.Parse("."));
			return (dbext.Length == 2)
				? conn.GetSchema("Columns", new string[] { databaseName, dbext[0], dbext[1] })
				: conn.GetSchema("Columns", new string[] { databaseName, null, dbext[0] });
		}

		public static void GetTableKeys(string databaseName, string tableName, SqlConnection conn, out string[] keys, out bool hasKeys) {
			string[] dbext = tableName.Split(char.Parse("."));

			SqlCommand cmd = conn.CreateCommand();
			cmd.CommandText = "SELECT * FROM " + tableName;

			SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
			DataTable tableSchema = reader.GetSchemaTable();
			reader.Close();

			//setup restrictions to pull primary keys out of the IndexColumns
			String[] restrictions = new string[5];
			restrictions[0] = databaseName; //database name
			restrictions[1] = null; //owner of database
			restrictions[2] = null; //table name
			restrictions[3] = null; //constraint name
			restrictions[4] = null; //column name

			if (dbext.Length == 2) {
				restrictions[1] = dbext[0];
				restrictions[2] = dbext[1];
			}
			else {
				restrictions[2] = dbext[0];
			}

			//multiple prefixes are used for primary keys in the indexes
			//need to find a better way to determine primary keys
			restrictions[3] = "XPK" + restrictions[2];
			DataTable keysSchema = conn.GetSchema("IndexColumns", restrictions);
			if (keysSchema.Rows.Count == 0) {
				restrictions[3] = "PK" + restrictions[2];
				keysSchema = conn.GetSchema("IndexColumns", restrictions);
			}
			if (keysSchema.Rows.Count == 0) {
				restrictions[3] = "PK_" + restrictions[2];
				keysSchema = conn.GetSchema("IndexColumns", restrictions);
			}

			//create an array of the 'primary keys'
			keys = new string[keysSchema.Rows.Count];

			for (int index = 0; index < keysSchema.Rows.Count; index++) {
				keys[index] = (string)keysSchema.Rows[index]["COLUMN_NAME"];
			}

			hasKeys = (keys.Length > 0 && tableSchema.Select("IsIdentity = true").Length > 0);
		}
	}
}
